﻿namespace DataAccessLogic
{
    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Web;
    using System.Data.SqlClient;
    using BusinessLogic;
    using System.Data;
    using System.Web.Security;
    using System.Reflection;

    public static class DataAccess
    {
        private static string _connectionStringKey = "SEP";

        public static string CheckForStringColumn(DataRow row, string columnName)
        {
            return CheckForNullColumn<string>(row, columnName);
        }

        public static bool CheckForBooleanColumn(DataRow row, string columnName)
        {
            return CheckForNullColumn<bool>(row, columnName);
        }

        public static Guid CheckForGuidColumn(DataRow row, string columnName)
        {
            return CheckForNullColumn<Guid>(row, columnName);
        }

        public static DateTime CheckForDateTimeColumn(DataRow row, string columnName)
        {
            return CheckForNullColumn<DateTime>(row, columnName);
        }

        private static T CheckForNullColumn<T>(DataRow row, string columnName)
        {
            if (row.IsNull(columnName))
            {
                return default(T);
            }
            else
            {
                return (T)row[columnName];
            }

        }

        public static void Create(string storedProcedure, List<SqlParameter> parms)
        {
            ExecuteNonQuery(storedProcedure, parms);
        }

        public static object Create(string storedProcedure, string parameterName, List<SqlParameter> parms)
        {
            object result = null;

            DatabaseWrapper db = ExecuteNonQuery(storedProcedure, parms);

            if (null != db)
            {
                result = db.OutputParameterValue(parameterName);
            }

            return result;
        }

        public static void Delete(string storedProcedure, List<SqlParameter> parms)
        {
            ExecuteNonQuery(storedProcedure, parms);
        }

        public static DataTable Get(string storedProcedure)
        {
            return Get(storedProcedure, null);
        }

        public static DataTable Get(string storedProcedure, List<SqlParameter> parms)
        {
            DataTable dt = null;

            if (!string.IsNullOrWhiteSpace(storedProcedure))
            {
                DatabaseWrapper db = new DatabaseWrapper(_connectionStringKey, storedProcedure, parms);
                dt = db.ExecuteDataTable();
            }

            return dt;
        }

        public static void Update(string storedProcedure, List<SqlParameter> parms)
        {
            ExecuteNonQuery(storedProcedure, parms);
        }

        private static DatabaseWrapper ExecuteNonQuery(string storedProcedure, List<SqlParameter> parms)
        {
            DatabaseWrapper db = null;

            if (!string.IsNullOrWhiteSpace(storedProcedure))
            {
                db = new DatabaseWrapper(_connectionStringKey, storedProcedure, parms);
                db.ExecuteNonQuery();
            }

            return db;
        }

        /// <summary>
        /// Takes an object and a data row
        /// Binds row data from the database to the properties of the object.
        /// For this to work, the attributes of each property on the object must describe
        /// how the property relates to database field names.
        /// 
        /// Here is an example of how those attributes would look:
        /// [SQLParameter(ColumnName = "STATEABBREVIATION", SQLDoWhat = SQLDoWhatEnum.Get, TypeOf = typeof(State))]
        /// [SQLParameter(ColumnName = "COUNTRYABBREVIATION", SQLDoWhat = SQLDoWhatEnum.Get, TypeOf = typeof(Country))]
        /// [SQLParameter(ParameterName = "@ABBREVIATION", SQLDoWhat = SQLDoWhatEnum.Create)]
        /// public string Abbreviation  //this is the property we are going to bind to row data
        /// </summary>
        /// <param name="row">A row of data from the database</param>
        /// <param name="obj">An object whose properties map to that row data, with attributes that describe how they map.</param>
        /// <returns></returns>
        private static object BindObjectToRowData(DataRow row, object obj)
        {
            object BoundObject = obj;
            IList<PropertyInfo> properties = new List<PropertyInfo>(BoundObject.GetType().GetProperties());

            foreach (PropertyInfo property in properties)
            {
                object[] attributes = property.GetCustomAttributes(true);
                foreach (object attribute in attributes)
                {
                    if (attribute is SQLParameterAttribute)
                    {
                        SQLParameterAttribute sqlAttribute = (SQLParameterAttribute)attribute;
                        if (!string.IsNullOrWhiteSpace(sqlAttribute.ColumnName) || sqlAttribute.IsClass)
                        {
                            //if the property is itself another class, then we will bind it to the row data too
                            //we know its one of ours if the Get method returns a class
                            if (sqlAttribute.IsClass && property.GetGetMethod().ReturnType.IsClass)
                            {
                                //this property is a class. instantiate it (whatever it is), and bind it
                                object ourObject = property.PropertyType.GetConstructor(new Type[] { }).Invoke(new object[] { });

                                //whatever we just got back, bind it to the row data.
                                ourObject = BindObjectToRowData(row, ourObject);

                                //now bind whatever we got back to the properties of the parent object
                                property.SetValue(BoundObject, ourObject, null);
                            }
                            else if (SQLDoWhatEnum.Get == sqlAttribute.SQLDoWhat)
                            {
                                //bind the data from the row to our property
                                try
                                {
                                    if ((sqlAttribute.TypeOf == null) || (sqlAttribute.TypeOf == BoundObject.GetType()))
                                    {
                                        property.SetValue(BoundObject, row[sqlAttribute.ColumnName], null);
                                    }
                                }
                                catch { }
                            }
                        }  //if (!string.IsNullOrWhiteSpace(sqlAttribute.ColumnName))                        
                    }  //if (attribute is SQLParameterAttribute)
                }  //foreach attribute
            }  //foreach property
            return BoundObject;
        }

        public static void BindPropertyValuesFromDataRow(DataRow row, object obj)
        {
            obj = BindObjectToRowData(row, obj);
        }
        
        public static class Address_DA
        {
            public static Address GetByConstituentID(Guid constituentID)
            {
                Address a = null;

                List<Address> addresses = GetAllByConstituentID(constituentID);

                if (addresses != null && 0 < addresses.Count)
                {
                    a = addresses[0];
                }

                return a;
            }

            public static Address GetPrimaryByConstituentID(Guid constituentID)
            {
                List<SqlParameter> parms = new List<SqlParameter>();
                parms.Add(new SqlParameter("@CONSTITUENTID", constituentID));

                DatabaseWrapper db = new DatabaseWrapper(_connectionStringKey, StoredProcedures.USP_ADDRESS_GETPRIMARYBYCONSTITUENTID, parms);
                DataTable dt = db.ExecuteDataTable();

                Address a = null;

                if(dt != null && 0 < dt.Rows.Count)
                {
                    a = new Address();

                    BindPropertyValuesFromDataRow(dt.Rows[0], a);
                }
                
                return a;
            }

            public static List<Address> GetAllByConstituentID(Guid constituentID)
            {
                List<SqlParameter> parms = new List<SqlParameter>();
                parms.Add(new SqlParameter("@CONSTITUENTID", constituentID));

                List<Address> addresses = null;

                try
                {
                    DatabaseWrapper db = new DatabaseWrapper(_connectionStringKey, StoredProcedures.USP_ADDRESS_GETALLBYCONSTITUENTID, parms);
                    DataTable dt = db.ExecuteDataTable();

                    if (0 < dt.Rows.Count)
                    {
                        addresses = new List<Address>();

                        foreach (DataRow row in dt.Rows)
                        {
                            Address address = new Address();
                            BindPropertyValuesFromDataRow(row, address);
                            addresses.Add(address);
                        }
                    }
                }
                catch
                {
                    addresses = null;
                }

                return addresses;
            }

            public static List<State> GetStates()
            {
                List<State> states = null;

                try
                {
                    DatabaseWrapper db = new DatabaseWrapper(_connectionStringKey, StoredProcedures.USP_STATEFORMATTEDTYPECODE_GETALL);
                    DataTable dt = db.ExecuteDataTable();

                    if (0 < dt.Rows.Count)
                    {
                        states = new List<State>();

                        foreach (DataRow row in dt.Rows)
                        {
                            State s = new State();
                            BindPropertyValuesFromDataRow(row, s);
                            states.Add(s);
                        }
                    }
                }
                catch
                {
                    states = null;
                }

                return states;
            }

            public static List<Country> GetCountries()
            {
                List<Country> countries = null;

                try
                {
                    DatabaseWrapper db = new DatabaseWrapper(_connectionStringKey, StoredProcedures.USP_COUNTRYFORMATTEDTYPECODE_GETALL);
                    DataTable dt = db.ExecuteDataTable();

                    if (0 < dt.Rows.Count)
                    {
                        countries = new List<Country>();

                        foreach (DataRow row in dt.Rows)
                        {
                            Country s = new Country();
                            BindPropertyValuesFromDataRow(row, s);
                            countries.Add(s);
                        }
                    }
                }
                catch
                {
                    countries = null;
                }

                return countries;
            }
        }

        public static class Email_DA
        {
            public static Email GetByConstituentID(Guid constituentID)
            {
                Email e = null;

                List<Email> emails = GetAllByConstituentID(constituentID);

                if(emails != null && 0 < emails.Count)
                {
                    e = emails[0];
                }

                return e;
            }

            public static Email GetPrimaryByConstituentID(Guid constituentID)
            {
                List<SqlParameter> parms = new List<SqlParameter>();
                parms.Add(new SqlParameter("@CONSTITUENTID", constituentID));

                DatabaseWrapper db = new DatabaseWrapper(_connectionStringKey, StoredProcedures.USP_EMAILADDRESS_GETPRIMARYBYCONSTITUENTID, parms);
                DataTable dt = db.ExecuteDataTable();

                Email e = null;

                if (dt != null && 0 < dt.Rows.Count)
                {
                    e = new Email();
                    BindPropertyValuesFromDataRow(dt.Rows[0], e);
                }

                return e;
            }

            public static List<Email> GetAllByConstituentID(Guid constituentID)
            {
                List<SqlParameter> parms = new List<SqlParameter>();
                parms.Add(new SqlParameter("@CONSTITUENTID", constituentID));

                List<Email> emails = null;

                try
                {
                    DatabaseWrapper db = new DatabaseWrapper(_connectionStringKey, StoredProcedures.USP_EMAILADDRESS_GETALLBYCONSTITUENTID, parms);
                    DataTable dt = db.ExecuteDataTable();

                    if (0 < dt.Rows.Count)
                    {
                        emails = new List<Email>();

                        foreach (DataRow row in dt.Rows)
                        {
                            Email email = new Email();
                            BindPropertyValuesFromDataRow(row, email);
                            emails.Add(email);
                        }
                    }
                }
                catch
                {
                    emails = null;
                }

                return emails;
            }
        }

        public static class Phone_DA
        {
            public static Phone GetByConstituentID(Guid constituentID)
            {
                Phone p = null;

                List<Phone> phones = GetAllByConstituentID(constituentID);

                if (phones != null && 0 < phones.Count)
                {
                    p = phones[0];
                }

                return p;
            }

            public static Phone GetPrimaryByConstituentID(Guid constituentID)
            {
                List<SqlParameter> parms = new List<SqlParameter>();
                parms.Add(new SqlParameter("@CONSTITUENTID", constituentID));

                DatabaseWrapper db = new DatabaseWrapper(_connectionStringKey, StoredProcedures.USP_PHONE_GETPRIMARYBYCONSTITUENTID, parms);
                DataTable dt = db.ExecuteDataTable();

                Phone p = null;

                if (dt != null && 0 < dt.Rows.Count)
                {
                    p = new Phone();
                    BindPropertyValuesFromDataRow(dt.Rows[0], p);
                }

                return p;
            }

            public static List<Phone> GetAllByConstituentID(Guid constituentID)
            {
                List<SqlParameter> parms = new List<SqlParameter>();
                parms.Add(new SqlParameter("@CONSTITUENTID", constituentID));

                List<Phone> phones = null;

                try
                {
                    DatabaseWrapper db = new DatabaseWrapper(_connectionStringKey, StoredProcedures.USP_PHONE_GETALLBYCONSTITUENTID, parms);
                    DataTable dt = db.ExecuteDataTable();

                    if (0 < dt.Rows.Count)
                    {
                        phones = new List<Phone>();

                        foreach (DataRow row in dt.Rows)
                        {
                            Phone phone = new Phone();
                            BindPropertyValuesFromDataRow(row, phone);
                            phones.Add(phone);
                        }
                    }
                }
                catch
                {
                    phones = null;
                }

                return phones;
            }
        }

        public static class Constituent_DA
        {
            public static List<Constituent> GetActive()
            {
                return Get(StoredProcedures.USP_CONSTITUENT_GETACTIVE);
            }

            public static List<Constituent> GetAll()
            {
                return Get(StoredProcedures.USP_CONSTITUENT_GETALL);
            }

            public static List<Constituent> GetActivePrimaries()
            {
                return Get(StoredProcedures.USP_CONSTITUENT_GETACTIVEPRIMARIES);
            }

            private static List<Constituent> Get(string storedProcedure)
            {
                List<Constituent> constituents = null;

                DataTable dt = DataAccess.Get(storedProcedure);

                if (null != dt)
                {
                    constituents = new List<Constituent>();

                    foreach (DataRow row in dt.Rows)
                    {
                        Constituent c = new Constituent(DataAccess.CheckForGuidColumn(row, "ID"));
                        constituents.Add(c);
                    }
                }

                return constituents;
            }
        }

        public static class AppUser_DA
        {
            public static List<AppUser> GetActive()
            {
                return Get(StoredProcedures.USP_APPUSER_GETACTIVE);
            }

            public static List<AppUser> GetAll()
            {
                return Get(StoredProcedures.USP_APPUSER_GETALL);
            }

            private static List<AppUser> Get(string storedProcedure)
            {
                List<AppUser> appUsers = null;

                DataTable dt = DataAccess.Get(storedProcedure);

                if (null != dt)
                {
                    appUsers = new List<AppUser>();

                    foreach (DataRow row in dt.Rows)
                    {
                        AppUser au = new AppUser();
                        BindPropertyValuesFromDataRow(row, au);
                        appUsers.Add(au);
                    }
                }

                return appUsers;
            }
        }

        public static class CodeTable_DA
        {
            public static List<AddressType> GetAllAddressTypes()
            {
                List<AddressType> codes = null;

                DataTable dt = Get(StoredProcedures.USP_ADDRESSTYPECODE_GETALL);

                if (null != dt)
                {
                    codes = new List<AddressType>();

                    foreach (DataRow row in dt.Rows)
                    {
                        AddressType addressType = new AddressType();
                        BindPropertyValuesFromDataRow(row, addressType);
                        codes.Add(addressType);
                    }
                }

                return codes;
            }

            public static List<PhoneType> GetAllPhoneTypes()
            {
                List<PhoneType> codes = null;

                DataTable dt = Get(StoredProcedures.USP_PHONETYPECODE_GETALL);

                if (null != dt)
                {
                    codes = new List<PhoneType>();

                    foreach (DataRow row in dt.Rows)
                    {
                        PhoneType phoneType = new PhoneType();
                        BindPropertyValuesFromDataRow(row, phoneType);
                        codes.Add(phoneType);
                    }
                }

                return codes;
            }

            public static List<EmailType> GetAllEmailTypes()
            {
                List<EmailType> codes = null;

                DataTable dt = Get(StoredProcedures.USP_EMAILADDRESSTYPECODE_GETALL);

                if (null != dt)
                {
                    codes = new List<EmailType>();

                    foreach (DataRow row in dt.Rows)
                    {
                        EmailType emailType = new EmailType();
                        BindPropertyValuesFromDataRow(row, emailType);
                        codes.Add(emailType);
                    }
                }

                return codes;
            }
        }
    }
}